<?php

/**
 * @author ducnhat
 * @email ddnhat@gmail.com
 * @copyright 2014
 */

class Admin_Model_BackupDatabase extends Zend_Db_Table{
    
    public function createSQLFile(){
        $stmt = "\r\n-- ----------------------------\r\n";
        $stmt .= "-- Hanvy Data Transfer\r\n";
        $stmt .= "-- Date: " . date("Y-m-d H:i:s") . "\r\n";
        $stmt .= "-- Database: " . $this->getDbName() . "\r\n";
        $stmt .= "-- ----------------------------\r\n";
        
        $tables = $this->getTables();
        
        foreach($tables as $table){
            $stmt .= $this->createTableStatement($table);
        }
        
        return $stmt;
    }
    
    public function getDbName(){
        $db = Zend_Registry::get('db');
        $dbConfig = $db->getConfig();
        $dbName = $dbConfig['dbname'];
        
        return $dbName;
    }
    
    private function getTables(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $select = "SHOW TABLES";
        
        $query = $db->query($select);
        
        $tables = $query->fetchAll();
        
        $rs = array();
        
        foreach($tables as $table){
            $rs[] =  current($table);
        }
        
        return $rs;
    }
    
    private function createTableStatement($table){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $select = "SHOW COLUMNS FROM `$table`";
        
        $query = $db->query($select);
        
        $cols = $query->fetchAll();
        
        $stmt = "\r\n-- ----------------------------\r\n";
        $stmt .= "-- Table structure for `order_buys`\r\n";
        $stmt .= "-- ----------------------------\r\n";
        $stmt .= "DROP TABLE IF EXISTS `$table`;\r\n";
        $stmt .= "CREATE TABLE `$table`(\r\n";
        
        $primary_cols = array();
        $i = 0;
        
        foreach($cols as $col){
            $i ++;
            $field = $col['Field'];
            $type = $col['Type'];
            
            if($col['Null'] == "NO"){
                $null = "NOT NULL";
            }else{
                $null = "";
            }
            
            if($col['Key'] == "PRI"){
                $primary_cols[] = "`$field`";
            }
            
            if($col['Extra'] == "auto_increment"){
                $extra = "AUTO_INCREMENT";
            }else{
                $extra = "";
            }
            
            $default = "DEFAULT ";
            
            if($col['Default'] != null){
                if(substr($type, 0, 3) == 'bit'){
                    $default .= $col['Default'];
                }else{
                    $default .= "'" . $col['Default'] . "'";
                }
            }else{
                $default .= "NULL";
                
                if($null == "NOT NULL"){
                    $default = "";
                }
            }
            
            $comma = ",";
            
            if($i == count($cols)){
                $comma = "";
            }
            
            $stmt .= "\t`$field` $type $null $key $extra $default,\r\n";
        }
        
        $primary_cols = implode(", ", $primary_cols);
        $stmt .= "\tPRIMARY KEY($primary_cols)\r\n";
        $stmt .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;\r\n";
        
        $stmt .= $this->createRecordsStatement($table);
        
        return $stmt;
    }
    
    public function createRecordsStatement($table){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $select = "SELECT * FROM `$table`";
        
        $query = $db->query($select);
        
        $data = $query->fetchAll();
        
        if(count($data) > 0){
            $stmt = "\r\n-- ----------------------------\r\n";
            $stmt .= "-- Records for table `$table`\r\n";
            $stmt .= "-- ----------------------------\r\n";
            
            foreach($data as $row){
                $i = 0;
                $stmt .= "INSERT INTO `$table` VALUES(";
                
                foreach($row as $record){
                    $i ++;
                    
                    $comma = ", ";
                    
                    if($i == count($row)){
                        $comma = "";
                    }
                    
                    $stmt .= "'" . $record . "'" . $comma;
                    
                }
                
                $stmt .= ");\r\n";
            }
        }else{
            $stmt = "";
        }
        
        return $stmt;
    }
}